SET XACT_ABORT OFF SET ARITHABORT OFF /* DECLARE GLOBAL VARIABLES */ DECLARE @rptPK int DECLARE @rptGroupPK int DECLARE @tempPK int DECLARE @errorcount int DECLARE @dsql varchar(8000) DECLARE @version decimal(9,2) SELECT @version = CAST(REPLACE(LOWER(schema_version), 'sp', '') AS decimal(9,2)) FROM _schema /* ===================================================== UPDATE REPORTS STYLES AND GROUP HEADERS - (RPTLISTSmartAudit) ===================================================== */ IF @version >= 3.0 BEGIN EXEC('PRINT ''Version 3.0 or greater detected. Importing Report Styles and Group Headers'' DECLARE @tempPK int IF NOT EXISTS(SELECT ReportStyleName FROM ReportStyle WHERE ReportStyleName = ''Default'') BEGIN INSERT INTO ReportStyle (ReportStyleName, ReportStyleDesc, ReportStyleCSS, IsDefault, IsBase, RowVersionIPAddress, RowVersionUserPK, RowVersionInitials, RowVersionAction, RowVersionDate) VALUES(''Default'', null, '' .pageselect{FONT-SIZE: 9pt; COLOR: #333333; FONT-FAMILY: Arial} .heading {background-color:#ffffff; cursor:pointer; FONT-WEIGHT: bold; FONT-SIZE: 12px; COLOR: royalblue; FONT-FAMILY: Arial; z-index: 2500;} .legendHeader {FONT-WEIGHT: bold; FONT-SIZE: 14px; COLOR: #333333; FONT-FAMILY: Arial} .normaltext {FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial} .labels {FONT-WEIGHT: bold; FONT-SIZE: 12px; COLOR: royalblue; FONT-FAMILY: Arial} .assetUP {FONT-WEIGHT: bold; FONT-SIZE: 12px; COLOR: green; FONT-FAMILY: Arial} .assetDOWN {FONT-WEIGHT: bold; FONT-SIZE: 12px; COLOR: #DD0000; FONT-FAMILY: Arial} .asset {FONT-WEIGHT: normal; FONT-SIZE: 12px; COLOR: #000000; FONT-FAMILY: Arial} .data {FONT-SIZE: 12px; COLOR: #494949; FONT-FAMILY: Arial} .data_underline {BORDER-RIGHT: medium none; BORDER-TOP: medium none; FONT-SIZE: 12px; BORDER-LEFT: medium none; COLOR: #494949; BORDER-BOTTOM: #333333 1px solid; FONT-FAMILY: Arial} .bottomline {BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: #333333 1px solid} .buttons {FONT-SIZE: 12px; WIDTH: 80px; cursor: pointer; COLOR: #333333; FONT-FAMILY: Arial} .subtotal {BORDER-RIGHT: medium none; BORDER-TOP: #C0C0C0 1px solid; FONT-SIZE: 12px; BORDER-LEFT: medium none; COLOR: #333333; BORDER-BOTTOM: medium none; FONT-FAMILY: Arial} .bodyclasspreview {background-color:#ffffff; padding:10px; scrollbar-base-color: #FBFBFB; font-size:8pt; font-family:Arial; color:#000000;} .bodyclasspreviewinwo {background-color:#ffffff; padding-right:10px; scrollbar-base-color: #FBFBFB; font-size:8pt; font-family:Arial; color:#000000;} .bodyclassprint {background-color:#ffffff; PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; PADDING-TOP: 0px; font-size:8pt; font-family:Arial; color:#000000;} .bodyclassemail {background-color:#ffffff; PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-BOTTOM: 20px; PADDING-TOP: 0px; font-size:8pt; font-family:Arial; color:#000000;} .group1 {padding-left:0px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; FONT-WEIGHT: Bold; BACKGROUND-COLOR: #acc5e7;} .group2 {padding-left:10px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; FONT-WEIGHT: Bold; BACKGROUND-COLOR: #c7d7ed;} .group3 {padding-left:20px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; FONT-WEIGHT: Bold; BACKGROUND-COLOR: #dce8f4;} .group4 {padding-left:30px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; FONT-WEIGHT: Bold; BACKGROUND-COLOR: #ecf1fb;} .group5 {padding-left:40px; FONT-SIZE: 12px; COLOR: royalblue; FONT-FAMILY: Arial; FONT-WEIGHT: Bold; BACKGROUND-COLOR: #ffffff;} .groupheader {FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; FONT-WEIGHT: Bold;} .normalright {BORDER-RIGHT: #c0c0c0 1px solid; BORDER-TOP: #c0c0c0 1px solid; PADDING-LEFT: 1px; FONT-WEIGHT: normal; FONT-SIZE: 8pt; MARGIN-BOTTOM: 1px; BORDER-LEFT: #c0c0c0 1px solid; COLOR: #000000; BORDER-BOTTOM: #c0c0c0 1px solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right} .clsBtnUp {cursor: pointer; color: black; font-weight: normal; border-top:1px solid #ffffff;border-left:1px solid #ffffff;border-right:1px solid #B4B4B4;border-bottom:1px solid #B4B4B4;padding-right:2px;} .clsBtnDown {cursor: pointer; color: black; font-weight: normal; border-right:1px solid #ffffff;border-bottom:1px solid #ffffff;border-top:1px solid #B4B4B4;border-left:1px solid #B4B4B4;padding-right:2px;} .clsBtnOff {color: black; font-weight: normal; tab-index: 0; border:1px solid transparent; padding-right:2px;} .actionbarlabel {float:right;margin-top:6px;padding-left:5px;padding-right:5px;font-size:8pt;font-family:Arial;color:#000000;} INPUT {padding-left:3px;} A:link {FONT-SIZE: 8pt; cursor: pointer; COLOR: #315aad; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent;} A:visited {FONT-SIZE: 8pt; cursor: pointer; COLOR: #315aad; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent;} A:active {FONT-SIZE: 8pt; cursor: pointer; COLOR: #315aad; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent;} A:hover {COLOR: red;} fieldset {border: 1px solid #AAAAAB;} .buttonsdisabled { display: static; opacity: 0.4; cursor:pointer; } .buttonsenabled { display: ; opacity: 1; cursor:pointer; } .normalrow {FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial } .tb {width:100%; PADDING-LEFT: 1px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; border: 1px solid #AAAAAB;} .tbf {BACKGROUND-COLOR: #ffffcc; width:100%; PADDING-LEFT: 1px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; border: 1px solid #AAAAAB;} .ta {width:200px; PADDING-LEFT: 1px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; border: 1px solid #AAAAAB;} .taf {BACKGROUND-COLOR: #ffffcc; width:200px; PADDING-LEFT: 1px; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; border: 1px solid #AAAAAB;} .cb {COLOR: #333333;} .HeaderRight {font-family:Arial;font-size:16px;color:#333333;font-weight:bold} .SubHeaderRight {font-family:Arial;font-size:11px;font-weight:normal} .SRInstructions {margin-top:5px;font-family:Arial;font-size:8pt;color:green;font-weight:bold} .verticalcolumn {border:1px solid #CCCCCC;} .mcpagebreak {page-break-before: always;} .ReportRow1 {background-color:#FFFFFF; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial } .ReportRow2 {background-color:#EFEFEF; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial } .ReportRowCrit1 {background-color:#FFFFFF; FONT-SIZE: 8pt; COLOR: #333333; FONT-FAMILY: Arial } .ReportRowCrit2 {background-color:#EFEFEF; FONT-SIZE: 8pt; COLOR: #333333; FONT-FAMILY: Arial } .SmartRow {background-color:#FFDF84; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial; cursor:pointer; } .SubReportRow {background-color:#DEEFC6; FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial } .ExpandCollapse {font-family:Arial;font-size:8pt;color:#333333;cursor:pointer;}'', 0, 1, '''', '''', ''_MC'', '''', getdate()) END ') END /* ============================================================ ReportID: RPTLISTSmartAudit Report Name: Report Smart Button Value w/ fields ============================================================ */ IF EXISTS ( SELECT ReportName FROM Reports WITH (NOLOCK) WHERE ReportID = 'RPTLISTSmartAudit') BEGIN PRINT '*******************************************************' PRINT 'Report Exists - Updating...' PRINT 'Report: Report Smart Button Value w/ fields' PRINT '*******************************************************' /* ================================================ UPDATE REPORT RECORDS - (RPTLISTSmartAudit) ================================================ */ /* Set ReportPK for this Report */ SELECT @rptPK = ReportPK FROM Reports WITH (NOLOCK) WHERE ReportID='RPTLISTSmartAudit' /* Update Main Report Fields */ UPDATE Reports SET [ReportIDPriorToCopy]='RPTLISTSmartAudit', [ReportDesc]='This report will display the Smart Button Field Value. As a general rule, this field should be the PK of the table being edited. For example, any change to a WO should display WO.WOPK in the Smart Button Field Value column.

For more information clik here: Editable smart reports

', [Sort1]='ReportGroup.ReportGroupName', [Sort2]='Reports.ReportName', [Sort3]='Reports.ReportID', [Sort4]=null, [Sort5]=null, [Sort1DESC]=0, [Sort2DESC]=0, [Sort3DESC]=0, [Sort4DESC]=0, [Sort5DESC]=0, [Group1]=1, [Group2]=0, [Group3]=0, [Group4]=0, [Group5]=0, [Header1]=0, [Header2]=0, [Header3]=0, [Header4]=0, [Header5]=0, [GroupHeader1]=null, [GroupHeader2]=null, [GroupHeader3]=null, [GroupHeader4]=null, [GroupHeader5]=null, [Total1]=0, [Total2]=0, [Total3]=0, [Total4]=0, [Total5]=0, [Chart]=null, [ChartName]=null, [ChartField]='SORT1', [ChartSize]=null, [ReportFile]='rpt_generic1.asp', [FromSQL]='FROM Reports', [JoinSQL]='LEFT JOIN ReportFields with(nolock) ON ReportFields.ReportPK = Reports.ReportPK AND ReportFields.SLAction = ''EF'' LEFT JOIN Report_ReportGroup ON Report_ReportGroup.REportPK = Reports.ReportPK LEFT JOIN ReportGroup ON ReportGroup.ReportGroupPK = Report_ReportGroup.ReportGroupPK', [WhereSQL]='(Reports.USEDFOR = ''REPORTS'' or Reports.USEDFOR Is NULL) AND Reports.SDDisplay != '''' AND Reports.SDDisplay IS NOT NULL AND (Reports.SDPKField != (SELECT TOP 1 TABLE_NAME + ''.'' + COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 AND TABLE_NAME = ReportFields.RFTable) OR ReportFields.RFTable IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 GROUP BY TABLE_NAME HAVING COUNT(COLUMN_NAME) > 1))', [GroupBy]=0, [hits]=37, [Sequence]=0, [Layout]='hor', [VertCols]=1, [PageBreakEachRecord]=0, [Custom]=0, [ReportCopy]=1, [MCRegistrationDB]=0, [PrintCriteria]=1, [Active]=1, [UDFChar1]=null, [UDFChar2]=null, [UDFChar3]=null, [UDFChar4]='N', [UDFChar5]=null, [UDFDate1]=null, [UDFDate2]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionAction]='EDIT', [RowVersionDate]=getdate() , [ChartFunction]=null, [ChartFunctionField]=null, [NoDetail]=0, [PB1]=0, [PB2]=0, [PB3]=0, [PB4]=0, [PB5]=0, [SLDefault]=0, [SLType]=' ', [SLAction]='PW', [SLModuleID]=' ', [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLTooltip]=null, [SDDisplay]=' ', [SDModuleID]=' ', [SDPKField]=null, [SmartEmail]=0, [ChartPosition]=null, [ChartFormat]='I', [ChartSQL]=null, [Chart2]=null, [ChartName2]=null, [ChartField2]=null, [ChartSize2]=null, [ChartFormat2]=null, [ChartFunction2]=null, [ChartFunctionField2]=null, [ChartPosition2]=null, [ChartSQL2]=null, [Chart3]=null, [ChartName3]=null, [ChartField3]=null, [ChartSize3]=null, [ChartFormat3]=null, [ChartFunction3]=null, [ChartFunctionField3]=null, [ChartPosition3]=null, [ChartSQL3]=null, [ChartOnly]=0, [NoHeader]=0, [SRID1]=null, [SRPKField1]=null, [SRID2]=null, [SRPKField2]=null, [SRID3]=null, [SRPKField3]=null, [SRID4]=null, [SRPKField4]=null, [SRID5]=null, [SRPKField5]=null, [ReportPageSize]='Default', [ReportWidth]='100%', [PhotoCriteria]=0, [ReportStyleName]='Default', [UsedFor]='REPORTS', [SmartEmailLaborPK]=0, [SCDefault]='H', [SCField1]=null, [SCField2]=null, [SCField3]=null, [ReportStyleFontSize]=null, [ReportStyleFontColor]=null, [ReportStyleFontFamily]=null WHERE ReportPK = @rptPK IF (@@error > 0) SET @errorcount = @errorcount + 1 IF @version >= 3.0 BEGIN SET @dSQL = 'UPDATE Reports SET [HavingSQL]=null, [DisplayPivotBar]=0, [DisplayColumnLines]=0, [DisplayTitleonPageBreak]=0, [DisplayFormatCriteria]=0, [R1T]='' '', [R1O]=''SQL '', [R1V1]=null, [R1V2]=null, [R1A]=2, [R1L]=0, [R1F]=0, [R1CS]=''border: #0066CC 2px solid;'', [R1AF]=''C'', [R2T]='' '', [R2O]=null, [R2V1]=null, [R2V2]=null, [R2A]=0, [R2L]=0, [R2F]=0, [R2CS]=''border: #0066CC 2px solid;'', [R2AF]=''C'', [R3T]='' '', [R3O]=null, [R3V1]=null, [R3V2]=null, [R3A]=0, [R3L]=0, [R3F]=0, [R3CS]=''border: #0066CC 2px solid;'', [R3AF]=''C'' WHERE ReportPK = ' + CAST(@rptPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END IF @version >= 4.2 BEGIN SET @dSQL = 'UPDATE Reports SET [DisplayDescription]=0 WHERE ReportPK = ' + CAST(@rptPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END PRINT 'Updating Report - RPTLISTSmartAudit' /* ================================================== DELETE AND INSERT REPORT GROUPS - (RPTLISTSmartAudit) =================================================== */ DELETE FROM Report_ReportGroup WHERE ReportPK = @rptPK PRINT 'Deleting Report_ReportGroup Rows - RPTLISTSmartAudit' IF (@@error > 0) SET @errorcount = @errorcount + 1 /* Make sure the report group actually exists */ IF NOT EXISTS (SELECT ReportGroupPK FROM ReportGroup WHERE ReportGroupPK = 54) BEGIN INSERT INTO ReportGroup ([ReportGroupID], [ReportGroupName], [ModuleID], [Sequence], [Icon], [RepairCenterPK], [IsUserGroup], [IsBatchGroup], [UDFChar1], [UDFChar2], [UDFChar3], [UDFChar4], [UDFChar5], [UDFDate1], [UDFDate2], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionAction], [RowVersionDate]) VALUES ('SY', 'System', 'SY', 99, null, null, 0, 0, null, null, null, null, null, null, null, null, '', '', '_MC', 'CREATE', getdate()) PRINT 'Inserting ReportGroup Row - ' + 'System' IF (@@error > 0) SET @errorcount = @errorcount + 1 SET @rptGroupPK = @@IDENTITY END ELSE BEGIN UPDATE ReportGroup SET [ReportGroupID]='SY', [ReportGroupName]='System', [ModuleID]='SY', [Sequence]=99, [Icon]=null, [RepairCenterPK]=null, [IsUserGroup]=0, [IsBatchGroup]=0, [UDFChar1]=null, [UDFChar2]=null, [UDFChar3]=null, [UDFChar4]=null, [UDFChar5]=null, [UDFDate1]=null, [UDFDate2]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionAction]='EDIT', [RowVersionDate]=getdate() WHERE ReportGroupPK = 54 PRINT 'Updating ReportGroup Row - ' + 'System' SET @rptGroupPK = 54 END IF (@@error > 0) SET @errorcount = @errorcount + 1 INSERT INTO Report_ReportGroup ([ReportPK], [ReportGroupPK], [DemoLaborPK], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, @rptGroupPK, '', '', '_MC', getdate()) PRINT 'Inserting Report_ReportGroup Row - ' + 'System' IF (@@error > 0) SET @errorcount = @errorcount + 1 /* ================================================== DELETE AND INSERT REPORT TABLES - (RPTLISTSmartAudit) =================================================== */ DELETE FROM ReportTables WHERE ReportPK = @rptPK PRINT 'Deleting ReportTables Rows - RPTLISTSmartAudit' IF (@@error > 0) SET @errorcount = @errorcount + 1 INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'ReportFields', null, 0, null, null, '', '', '_MC', getdate()) PRINT 'Inserting ReportTables Row - ReportFields' IF (@@error > 0) SET @errorcount = @errorcount + 1 INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'ReportGroup', null, 0, null, null, '', '', '_MC', getdate()) PRINT 'Inserting ReportTables Row - ReportGroup' IF (@@error > 0) SET @errorcount = @errorcount + 1 INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', null, 0, null, null, '', '', '_MC', getdate()) PRINT 'Inserting ReportTables Row - Reports' IF (@@error > 0) SET @errorcount = @errorcount + 1 /* ================================================== UPDATE OR INSERT REPORT CRITERIA - (RPTLISTSmartAudit) =================================================== */ IF EXISTS ( SELECT ReportCriteriaPK FROM ReportCriteria WITH (NOLOCK) WHERE ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'ReportID') BEGIN UPDATE ReportCriteria SET [SQLWhereTable]='Reports', [SQLWhereField]='ReportID', [CritName]=null, [Operator]=null, [isMulti]=0, [AskLater]=1, [LabelOverride]=null, [DisplayOrder]=0, [FK_LookupOverride]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate() WHERE ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'ReportID' IF (@@error > 0) SET @errorcount = @errorcount + 1 PRINT 'Updating ReportCriteria Row - Reports.ReportID' END ELSE BEGIN INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'ReportID', 'Reports', 'ReportID', null, null, null, 0, 1, null, 0, null, null, null, null, '_MC', getdate()) PRINT 'Inserting ReportCriteria Row - Reports.ReportID' IF (@@error > 0) SET @errorcount = @errorcount + 1 END IF EXISTS ( SELECT ReportCriteriaPK FROM ReportCriteria WITH (NOLOCK) WHERE ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'ReportName') BEGIN UPDATE ReportCriteria SET [SQLWhereTable]='Reports', [SQLWhereField]='ReportName', [CritName]=null, [Operator]=null, [isMulti]=0, [AskLater]=1, [LabelOverride]=null, [DisplayOrder]=1, [FK_LookupOverride]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate() WHERE ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'ReportName' IF (@@error > 0) SET @errorcount = @errorcount + 1 PRINT 'Updating ReportCriteria Row - Reports.ReportName' END ELSE BEGIN INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'ReportName', 'Reports', 'ReportName', null, null, null, 0, 1, null, 1, null, null, null, null, '_MC', getdate()) PRINT 'Inserting ReportCriteria Row - Reports.ReportName' IF (@@error > 0) SET @errorcount = @errorcount + 1 END IF EXISTS ( SELECT ReportCriteriaPK FROM ReportCriteria WITH (NOLOCK) WHERE ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'Active') BEGIN UPDATE ReportCriteria SET [SQLWhereTable]='Reports', [SQLWhereField]='Active', [CritName]=null, [Operator]=null, [isMulti]=0, [AskLater]=1, [LabelOverride]=null, [DisplayOrder]=2, [FK_LookupOverride]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate() WHERE ReportPK = @rptPK AND DisplayTable = 'Reports' AND DisplayField = 'Active' IF (@@error > 0) SET @errorcount = @errorcount + 1 PRINT 'Updating ReportCriteria Row - Reports.Active' END ELSE BEGIN INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'Active', 'Reports', 'Active', null, null, null, 0, 1, null, 2, null, null, null, null, '_MC', getdate()) PRINT 'Inserting ReportCriteria Row - Reports.Active' IF (@@error > 0) SET @errorcount = @errorcount + 1 END /* ================================================== UPDATE OR INSERT REPORT FIELDS - (RPTLISTSmartAudit) =================================================== */ IF EXISTS ( SELECT ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'ReportName') BEGIN SELECT @tempPK = ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'ReportName' UPDATE ReportFields SET [ReportPK]=@rptPK, [DataDictPK]=3716, [AGFunction]=null, [Alias]=null, [DisplayOrder]=0, [Display]=1, [NotUserSelectable]=0, [LabelOverride]=null, [TotalIfSelected]=0, [BlankLineIfSelected]=0, [UseCustomExpression]=0, [CustomExpression]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate(), [SLAction]=null, [SLModuleID]=null, [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLToolTip]=null WHERE [ReportPK]=@rptPK AND [RFTable]='Reports' AND [RFField]='ReportName' IF (@@error > 0) SET @errorcount = @errorcount + 1 PRINT 'Updating ReportFields Row - Reports.ReportName' END ELSE BEGIN INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip]) VALUES(@rptPK, 3716, null, 'Reports', 'ReportName', null, 0, 1, 0, null, 0, 0, 0, null, null, '', '', '_MC', getdate(), null, null, null, null, null, null) PRINT 'Inserting ReportFields Row - Reports.ReportName' IF (@@error > 0) BEGIN SET @errorcount = @errorcount + 1 SET @tempPK = -1 END ELSE BEGIN SET @tempPK = @@IDENTITY END END IF @version >= 4.2 BEGIN SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = null, [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END IF EXISTS ( SELECT ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'SCField1') BEGIN SELECT @tempPK = ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'SCField1' UPDATE ReportFields SET [ReportPK]=@rptPK, [DataDictPK]=7994, [AGFunction]=null, [Alias]=null, [DisplayOrder]=1, [Display]=1, [NotUserSelectable]=0, [LabelOverride]='Smart Button Field Value', [TotalIfSelected]=0, [BlankLineIfSelected]=0, [UseCustomExpression]=1, [CustomExpression]='Reports.SDPKField', [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate(), [SLAction]=' ', [SLModuleID]=null, [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLToolTip]=null WHERE [ReportPK]=@rptPK AND [RFTable]='Reports' AND [RFField]='SCField1' IF (@@error > 0) SET @errorcount = @errorcount + 1 PRINT 'Updating ReportFields Row - Reports.SCField1' END ELSE BEGIN INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip]) VALUES(@rptPK, 7994, null, 'Reports', 'SCField1', null, 1, 1, 0, 'Smart Button Field Value', 0, 0, 1, 'Reports.SDPKField', null, '', '', '_MC', getdate(), ' ', null, null, null, null, null) PRINT 'Inserting ReportFields Row - Reports.SCField1' IF (@@error > 0) BEGIN SET @errorcount = @errorcount + 1 SET @tempPK = -1 END ELSE BEGIN SET @tempPK = @@IDENTITY END END IF @version >= 4.2 BEGIN SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = '' '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END IF EXISTS ( SELECT ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'ReportFields' AND RFField = 'RFTable') BEGIN SELECT @tempPK = ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'ReportFields' AND RFField = 'RFTable' UPDATE ReportFields SET [ReportPK]=@rptPK, [DataDictPK]=3788, [AGFunction]=null, [Alias]=null, [DisplayOrder]=2, [Display]=1, [NotUserSelectable]=0, [LabelOverride]='Table saving to', [TotalIfSelected]=0, [BlankLineIfSelected]=0, [UseCustomExpression]=0, [CustomExpression]=null, [DemoLaborPK]=0, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate(), [SLAction]=' ', [SLModuleID]=null, [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLToolTip]=null WHERE [ReportPK]=@rptPK AND [RFTable]='ReportFields' AND [RFField]='RFTable' IF (@@error > 0) SET @errorcount = @errorcount + 1 PRINT 'Updating ReportFields Row - ReportFields.RFTable' END ELSE BEGIN INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip]) VALUES(@rptPK, 3788, null, 'ReportFields', 'RFTable', null, 2, 1, 0, 'Table saving to', 0, 0, 0, null, 0, '', '', '_MC', getdate(), ' ', null, null, null, null, null) PRINT 'Inserting ReportFields Row - ReportFields.RFTable' IF (@@error > 0) BEGIN SET @errorcount = @errorcount + 1 SET @tempPK = -1 END ELSE BEGIN SET @tempPK = @@IDENTITY END END IF @version >= 4.2 BEGIN SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = '' '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END IF EXISTS ( SELECT ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'ReportFields' AND RFField = 'RFField') BEGIN SELECT @tempPK = ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'ReportFields' AND RFField = 'RFField' UPDATE ReportFields SET [ReportPK]=@rptPK, [DataDictPK]=3789, [AGFunction]=null, [Alias]=null, [DisplayOrder]=3, [Display]=1, [NotUserSelectable]=0, [LabelOverride]='Field to be Saved', [TotalIfSelected]=0, [BlankLineIfSelected]=0, [UseCustomExpression]=0, [CustomExpression]=null, [DemoLaborPK]=0, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate(), [SLAction]=' ', [SLModuleID]=null, [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLToolTip]=null WHERE [ReportPK]=@rptPK AND [RFTable]='ReportFields' AND [RFField]='RFField' IF (@@error > 0) SET @errorcount = @errorcount + 1 PRINT 'Updating ReportFields Row - ReportFields.RFField' END ELSE BEGIN INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip]) VALUES(@rptPK, 3789, null, 'ReportFields', 'RFField', null, 3, 1, 0, 'Field to be Saved', 0, 0, 0, null, 0, '', '', '_MC', getdate(), ' ', null, null, null, null, null) PRINT 'Inserting ReportFields Row - ReportFields.RFField' IF (@@error > 0) BEGIN SET @errorcount = @errorcount + 1 SET @tempPK = -1 END ELSE BEGIN SET @tempPK = @@IDENTITY END END IF @version >= 4.2 BEGIN SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = '' '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END IF EXISTS ( SELECT ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'UDFChar1') BEGIN SELECT @tempPK = ReportFieldPK FROM ReportFields WITH (NOLOCK) WHERE ReportPK = @rptPK AND RFTable = 'Reports' AND RFField = 'UDFChar1' UPDATE ReportFields SET [ReportPK]=@rptPK, [DataDictPK]=3725, [AGFunction]=null, [Alias]=null, [DisplayOrder]=4, [Display]=1, [NotUserSelectable]=0, [LabelOverride]='Save Table PK', [TotalIfSelected]=0, [BlankLineIfSelected]=0, [UseCustomExpression]=1, [CustomExpression]='(SELECT TOP 1 TABLE_NAME + ''.'' + COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 AND TABLE_NAME = ReportFields.RFTable)', [DemoLaborPK]=0, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionDate]=getdate(), [SLAction]=' ', [SLModuleID]=null, [SLPKField]=null, [SLReportID]=null, [SLCustomAction]=null, [SLToolTip]=null WHERE [ReportPK]=@rptPK AND [RFTable]='Reports' AND [RFField]='UDFChar1' IF (@@error > 0) SET @errorcount = @errorcount + 1 PRINT 'Updating ReportFields Row - Reports.UDFChar1' END ELSE BEGIN INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip]) VALUES(@rptPK, 3725, null, 'Reports', 'UDFChar1', null, 4, 1, 0, 'Save Table PK', 0, 0, 1, '(SELECT TOP 1 TABLE_NAME + ''.'' + COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 AND TABLE_NAME = ReportFields.RFTable)', 0, '', '', '_MC', getdate(), ' ', null, null, null, null, null) PRINT 'Inserting ReportFields Row - Reports.UDFChar1' IF (@@error > 0) BEGIN SET @errorcount = @errorcount + 1 SET @tempPK = -1 END ELSE BEGIN SET @tempPK = @@IDENTITY END END IF @version >= 4.2 BEGIN SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = '' '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END /* ===================================================== UPDATE REPORTS COLS IN DATA DICT - (RPTLISTSmartAudit) ===================================================== */ UPDATE DataDict SET [REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 WHERE TABLE_NAME = 'ReportFields' AND COLUMN_NAME = 'RFField' PRINT 'Updating DataDict Row - ReportFields.RFField' IF (@@error > 0) SET @errorcount = @errorcount + 1 UPDATE DataDict SET [REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 WHERE TABLE_NAME = 'ReportFields' AND COLUMN_NAME = 'RFTable' PRINT 'Updating DataDict Row - ReportFields.RFTable' IF (@@error > 0) SET @errorcount = @errorcount + 1 UPDATE DataDict SET [REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 WHERE TABLE_NAME = 'Reports' AND COLUMN_NAME = 'ReportName' PRINT 'Updating DataDict Row - Reports.ReportName' IF (@@error > 0) SET @errorcount = @errorcount + 1 UPDATE DataDict SET [REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 WHERE TABLE_NAME = 'Reports' AND COLUMN_NAME = 'UDFChar1' PRINT 'Updating DataDict Row - Reports.UDFChar1' IF (@@error > 0) SET @errorcount = @errorcount + 1 END /* REPORT DID NOT EXIST, CREATE ALL RECORDS */ ELSE BEGIN PRINT '*******************************************************' PRINT 'Report Does Not Exist - Inserting...' PRINT 'Report: Report Smart Button Value w/ fields' PRINT '*******************************************************' /* ===================================================== INSERT REPORT RECORDS - (RPTLISTSmartAudit) ===================================================== */ /* INSERT Main Report */ INSERT INTO Reports ([ReportIDPriorToCopy], [ReportID], [ReportName], [ReportDesc], [RepairCenterPK], [Sort1], [Sort2], [Sort3], [Sort4], [Sort5], [Sort1DESC], [Sort2DESC], [Sort3DESC], [Sort4DESC], [Sort5DESC], [Group1], [Group2], [Group3], [Group4], [Group5], [Header1], [Header2], [Header3], [Header4], [Header5], [GroupHeader1], [GroupHeader2], [GroupHeader3], [GroupHeader4], [GroupHeader5], [Total1], [Total2], [Total3], [Total4], [Total5], [Chart], [ChartName], [ChartField], [ChartSize], [ReportFile], [FromSQL], [JoinSQL], [WhereSQL], [GroupBy], [hits], [Sequence], [Layout], [VertCols], [PageBreakEachRecord], [Custom], [ReportCopy], [MCRegistrationDB], [PrintCriteria], [Active], [UDFChar1], [UDFChar2], [UDFChar3], [UDFChar4], [UDFChar5], [UDFDate1], [UDFDate2], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionAction], [RowVersionDate], [ChartFunction], [ChartFunctionField], [NoDetail], [PB1], [PB2], [PB3], [PB4], [PB5], [SLDefault], [SLType], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip], [SDDisplay], [SDModuleID], [SDPKField], [SmartEmail], [ChartPosition], [ChartFormat], [ChartSQL], [Chart2], [ChartName2], [ChartField2], [ChartSize2], [ChartFormat2], [ChartFunction2], [ChartFunctionField2], [ChartPosition2], [ChartSQL2], [Chart3], [ChartName3], [ChartField3], [ChartSize3], [ChartFormat3], [ChartFunction3], [ChartFunctionField3], [ChartPosition3], [ChartSQL3], [ChartOnly], [NoHeader], [HavingSQL], [SRID1], [SRPKField1], [SRID2], [SRPKField2], [SRID3], [SRPKField3], [SRID4], [SRPKField4], [SRID5], [SRPKField5], [ReportPageSize], [ReportWidth], [PhotoCriteria], [ReportStyleName], [UsedFor], [SmartEmailLaborPK], [SCDefault], [SCField1], [SCField2], [SCField3], [ReportStyleFontSize], [ReportStyleFontColor], [ReportStyleFontFamily] ) VALUES('RPTLISTSmartAudit', 'RPTLISTSmartAudit', 'Report Smart Button Value w/ fields', 'This report will display the Smart Button Field Value. As a general rule, this field should be the PK of the table being edited. For example, any change to a WO should display WO.WOPK in the Smart Button Field Value column.

For more information clik here: Editable smart reports

', null, 'ReportGroup.ReportGroupName', 'Reports.ReportName', 'Reports.ReportID', null, null, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, null, null, null, null, null, 0, 0, 0, 0, 0, null, null, 'SORT1', null, 'rpt_generic1.asp', 'FROM Reports', 'LEFT JOIN ReportFields with(nolock) ON ReportFields.ReportPK = Reports.ReportPK AND ReportFields.SLAction = ''EF'' LEFT JOIN Report_ReportGroup ON Report_ReportGroup.REportPK = Reports.ReportPK LEFT JOIN ReportGroup ON ReportGroup.ReportGroupPK = Report_ReportGroup.ReportGroupPK', '(Reports.USEDFOR = ''REPORTS'' or Reports.USEDFOR Is NULL) AND Reports.SDDisplay != '''' AND Reports.SDDisplay IS NOT NULL AND (Reports.SDPKField != (SELECT TOP 1 TABLE_NAME + ''.'' + COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 AND TABLE_NAME = ReportFields.RFTable) OR ReportFields.RFTable IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 GROUP BY TABLE_NAME HAVING COUNT(COLUMN_NAME) > 1))', 0, 0, 0, 'hor', 1, 0, 0, 1, 0, 1, 1, null, null, null, 'N', null, null, null, null, '', '', '_MC', 'CREATE', getdate(), null, null, 0, 0, 0, 0, 0, 0, 0, ' ', 'PW', ' ', null, null, null, null, ' ', ' ', null, 0, null, 'I', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0, 0, null, null, null, null, null, null, null, null, null, null, null, 'Default', '100%', 0, 'Default', 'REPORTS', 0, 'H', null, null, null, null, null, null) PRINT 'Inserting Report - RPTLISTSmartAudit' IF (@@error > 0) SET @errorcount = @errorcount + 1 SET @rptPK = @@Identity IF @version >= 3.0 BEGIN SET @dSQL = 'UPDATE Reports SET [HavingSQL]=null, [DisplayPivotBar]=0, [DisplayColumnLines]=0, [DisplayTitleonPageBreak]=0, [DisplayFormatCriteria]=0, [R1T]='' '', [R1O]=''SQL '', [R1V1]=null, [R1V2]=null, [R1A]=2, [R1L]=0, [R1F]=0, [R1CS]=''border: #0066CC 2px solid;'', [R1AF]=''C'', [R2T]='' '', [R2O]=null, [R2V1]=null, [R2V2]=null, [R2A]=0, [R2L]=0, [R2F]=0, [R2CS]=''border: #0066CC 2px solid;'', [R2AF]=''C'', [R3T]='' '', [R3O]=null, [R3V1]=null, [R3V2]=null, [R3A]=0, [R3L]=0, [R3F]=0, [R3CS]=''border: #0066CC 2px solid;'', [R3AF]=''C'' WHERE ReportPK = ' + CAST(@rptPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END IF @version >= 4.2 BEGIN SET @dSQL = 'UPDATE Reports SET [DisplayDescription]=0 WHERE ReportPK = ' + CAST(@rptPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END /* ================================================== UPDATE OR INSERT REPORT GROUPS - (RPTLISTSmartAudit) =================================================== */ /* Make sure the report group actually exists */ IF NOT EXISTS (SELECT ReportGroupPK FROM ReportGroup WHERE ReportGroupPK = 54) BEGIN INSERT INTO ReportGroup ([ReportGroupID], [ReportGroupName], [ModuleID], [Sequence], [Icon], [RepairCenterPK], [IsUserGroup], [IsBatchGroup], [UDFChar1], [UDFChar2], [UDFChar3], [UDFChar4], [UDFChar5], [UDFDate1], [UDFDate2], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionAction], [RowVersionDate]) VALUES ('SY', 'System', 'SY', 99, null, null, 0, 0, null, null, null, null, null, null, null, null, '', '', '_MC', 'CREATE', getdate()) IF (@@error > 0) SET @errorcount = @errorcount + 1 PRINT 'Inserting ReportGroup Row - ' + 'System' SET @rptGroupPK = @@IDENTITY END ELSE BEGIN UPDATE ReportGroup SET [ReportGroupID]='SY', [ReportGroupName]='System', [ModuleID]='SY', [Sequence]=99, [Icon]=null, [RepairCenterPK]=null, [IsUserGroup]=0, [IsBatchGroup]=0, [UDFChar1]=null, [UDFChar2]=null, [UDFChar3]=null, [UDFChar4]=null, [UDFChar5]=null, [UDFDate1]=null, [UDFDate2]=null, [DemoLaborPK]=null, [RowVersionIPAddress]='', [RowVersionUserPK]='', [RowVersionInitials]='_MC', [RowVersionAction]='EDIT', [RowVersionDate]=getdate() WHERE ReportGroupPK = 54 IF (@@error > 0) SET @errorcount = @errorcount + 1 PRINT 'Updating ReportGroup Row - ' + 'System' SET @rptGroupPK = 54 END INSERT INTO Report_ReportGroup ([ReportPK], [ReportGroupPK], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, @rptGroupPK, null, '', '', '_MC', getdate()) PRINT 'Inserting Report_ReportGroup Row - ' + 'System' IF (@@error > 0) SET @errorcount = @errorcount + 1 /* ================================================== INSERT REPORT TABLES- (RPTLISTSmartAudit) =================================================== */ INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'ReportFields', null, 0, null, null, '', '', '_MC', getdate()) PRINT 'Inserting ReportTables Row - ReportFields' IF (@@error > 0) SET @errorcount = @errorcount + 1 INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'ReportGroup', null, 0, null, null, '', '', '_MC', getdate()) PRINT 'Inserting ReportTables Row - ReportGroup' IF (@@error > 0) SET @errorcount = @errorcount + 1 INSERT INTO ReportTables ([ReportPK], [RFTable], [Alias], [DisplayOrder], [LabelOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', null, 0, null, null, '', '', '_MC', getdate()) PRINT 'Inserting ReportTables Row - Reports' IF (@@error > 0) SET @errorcount = @errorcount + 1 /* ================================================== INSERT REPORT CRITERIA - (RPTLISTSmartAudit) =================================================== */ INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'ReportID', 'Reports', 'ReportID', null, null, null, 0, 1, null, 0, null, null, '', '', '_MC', getdate()) PRINT 'Inserting ReportCriteria Row - Reports.ReportID' IF (@@error > 0) SET @errorcount = @errorcount + 1 INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'ReportName', 'Reports', 'ReportName', null, null, null, 0, 1, null, 1, null, null, '', '', '_MC', getdate()) PRINT 'Inserting ReportCriteria Row - Reports.ReportName' IF (@@error > 0) SET @errorcount = @errorcount + 1 INSERT INTO ReportCriteria ([ReportPK], [DisplayTable], [DisplayField], [SQLWhereTable], [SQLWhereField], [DefaultCritValue], [CritName], [Operator], [isMulti], [AskLater], [LabelOverride], [DisplayOrder], [FK_LookupOverride], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate]) VALUES (@rptPK, 'Reports', 'Active', 'Reports', 'Active', null, null, null, 0, 1, null, 2, null, null, '', '', '_MC', getdate()) PRINT 'Inserting ReportCriteria Row - Reports.Active' IF (@@error > 0) SET @errorcount = @errorcount + 1 /* ================================================== INSERT REPORT FIELDS - (RPTLISTSmartAudit) =================================================== */ INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip]) VALUES(@rptPK, 3716, null, 'Reports', 'ReportName', null, 0, 1, 0, null, 0, 0, 0, null, null, '', '', '_MC', getdate(), null, null, null, null, null, null) PRINT 'Inserting ReportFields Row - Reports.ReportName' IF (@@error > 0) SET @errorcount = @errorcount + 1 IF @version >= 4.2 BEGIN SET @tempPK = @@IDENTITY SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = null, [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip]) VALUES(@rptPK, 7994, null, 'Reports', 'SCField1', null, 1, 1, 0, 'Smart Button Field Value', 0, 0, 1, 'Reports.SDPKField', null, '', '', '_MC', getdate(), ' ', null, null, null, null, null) PRINT 'Inserting ReportFields Row - Reports.SCField1' IF (@@error > 0) SET @errorcount = @errorcount + 1 IF @version >= 4.2 BEGIN SET @tempPK = @@IDENTITY SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = '' '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip]) VALUES(@rptPK, 3788, null, 'ReportFields', 'RFTable', null, 2, 1, 0, 'Table saving to', 0, 0, 0, null, 0, '', '', '_MC', getdate(), ' ', null, null, null, null, null) PRINT 'Inserting ReportFields Row - ReportFields.RFTable' IF (@@error > 0) SET @errorcount = @errorcount + 1 IF @version >= 4.2 BEGIN SET @tempPK = @@IDENTITY SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = '' '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip]) VALUES(@rptPK, 3789, null, 'ReportFields', 'RFField', null, 3, 1, 0, 'Field to be Saved', 0, 0, 0, null, 0, '', '', '_MC', getdate(), ' ', null, null, null, null, null) PRINT 'Inserting ReportFields Row - ReportFields.RFField' IF (@@error > 0) SET @errorcount = @errorcount + 1 IF @version >= 4.2 BEGIN SET @tempPK = @@IDENTITY SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = '' '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END INSERT INTO ReportFields ([ReportPK], [DataDictPK], [AGFunction], [RFTable], [RFField], [Alias], [DisplayOrder], [Display], [NotUserSelectable], [LabelOverride], [TotalIfSelected], [BlankLineIfSelected], [UseCustomExpression], [CustomExpression], [DemoLaborPK], [RowVersionIPAddress], [RowVersionUserPK], [RowVersionInitials], [RowVersionDate], [SLAction], [SLModuleID], [SLPKField], [SLReportID], [SLCustomAction], [SLTooltip]) VALUES(@rptPK, 3725, null, 'Reports', 'UDFChar1', null, 4, 1, 0, 'Save Table PK', 0, 0, 1, '(SELECT TOP 1 TABLE_NAME + ''.'' + COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 AND TABLE_NAME = ReportFields.RFTable)', 0, '', '', '_MC', getdate(), ' ', null, null, null, null, null) PRINT 'Inserting ReportFields Row - Reports.UDFChar1' IF (@@error > 0) SET @errorcount = @errorcount + 1 IF @version >= 4.2 BEGIN SET @tempPK = @@IDENTITY SET @dSQL = 'UPDATE ReportFields SET [Alignment] = null, [AdditionalWidth] = null, [PivotSetup] = '' '', [AddPivotColumnsWithNoDataFrom] = null, [AddPivotColumnsWithNoDataFromCustom] = null, [Data_Type_Override] = null, [ColumnFormat] = null, [ColumnCS] = null, [GroupByCustomExpression] = 1 WHERE ReportFieldPK = ' + CAST(@tempPK AS varchar(20)) + ' ' EXEC(@dSQL) IF (@@error > 0) SET @errorcount = @errorcount + 1 END /* ===================================================== UPDATE REPORTS COLS IN DATA DICT - (RPTLISTSmartAudit) ===================================================== */ UPDATE DataDict SET [REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 WHERE TABLE_NAME = 'ReportFields' AND COLUMN_NAME = 'RFField' PRINT 'Updating DataDict Row - ReportFields.RFField' if (@@error > 0) Set @errorcount = @errorcount + 1 UPDATE DataDict SET [REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 WHERE TABLE_NAME = 'ReportFields' AND COLUMN_NAME = 'RFTable' PRINT 'Updating DataDict Row - ReportFields.RFTable' if (@@error > 0) Set @errorcount = @errorcount + 1 UPDATE DataDict SET [REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 WHERE TABLE_NAME = 'Reports' AND COLUMN_NAME = 'ReportName' PRINT 'Updating DataDict Row - Reports.ReportName' if (@@error > 0) Set @errorcount = @errorcount + 1 UPDATE DataDict SET [REPORT_NOSELECT]=null, [REPORT_EDIT]='TB', [REPORT_LABEL]=null, [TOTALIFSELECTED]=0 WHERE TABLE_NAME = 'Reports' AND COLUMN_NAME = 'UDFChar1' PRINT 'Updating DataDict Row - Reports.UDFChar1' if (@@error > 0) Set @errorcount = @errorcount + 1 END PRINT '*******************************************************' IF (@errorcount > 0) PRINT @errorcount + ' Error(s) Occurred: RPTLISTSmartAudit' ELSE PRINT 'No Errors Occurred: RPTLISTSmartAudit'